""""
Case Type   : D库常用语法兼容-proc
Case Name   : D库支持创建存储过程。owner to 后执行正常
Create At   : 2025-11-19
Owner       : chen
Description :
    1.创建proc
    2.create user
    3.修改存储过程所属用户
    4.修改存储过程所属用户不带()
    5.通过procedure 修改用户
    6.删除用户
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.合理报错
    5.成功
    6.合理报错
    7.成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class DDL_Proc_Case0003(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.dbname_d = Node('PrimaryDbDmode').db_name
        self.proc_name = 'p_proc_case003'
        self.user1_name = 'u_proc_user1_case003'
        self.user2_name = 'u_proc_user2_case003'

    def test_proc(self):
        text = '-----step1: 创建proc; expect: 成功-----'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_proc = f'''
            create proc {self.proc_name}()
            is
            begin
            RAISE INFO 'call procedure: {self.proc_name}';
            end;'''
        res = primary_sh.execut_db_sql(create_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, res)
        detail_sql = f'\df {self.proc_name}();'
        res = primary_sh.execut_db_sql(detail_sql, dbname=self.dbname_d)
        log.info(res)
        detail_list = ['public', self.proc_name, 'void', 'normal', 'f', 'p']
        for detail in detail_list:
            self.assertIn(detail, res)

        text = '-----step2: create user; expect: 成功-----'
        log.info(text)
        create_user = f"create user {self.user1_name} with password " \
            f"'{macro.COMMON_PASSWD}';" \
            f"grant all privileges to {self.user1_name};"
        res = primary_sh.execut_db_sql(create_user,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, res)
        self.assertIn(self.constant.ALTER_ROLE_SUCCESS_MSG, res)

        text = '-----step3: 修改存储过程所属用户; expect: 成功-----'
        log.info(text)
        find_sql = f"select usename from pg_user a, pg_proc b where " \
            f"a.usesysid = b.proowner and b.proname = '{self.proc_name}';"
        res = primary_sh.execut_db_sql(find_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(Node('PrimaryDbDmode').ssh_user, res)
        alter_sql = f'alter proc {self.proc_name}() owner to {self.user1_name};'
        res = primary_sh.execut_db_sql(alter_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.ALTER_FUNCTION_SUCCESS_MSG, res)
        res = primary_sh.execut_db_sql(find_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.user1_name, res)

        text = '-----step4: 修改存储过程所属用户不带(); expect: 合理报错-----'
        log.info(text)
        alter_sql = f'alter proc {self.proc_name} owner to {self.user1_name};'
        res = primary_sh.execut_db_sql(alter_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn('ERROR:  syntax error', res)

        text = '-----step5: 通过procedure 修改用户; expect: 成功-----'
        log.info(text)
        create_user = f"create user {self.user2_name} with password " \
            f"'{macro.COMMON_PASSWD}';" \
            f"grant all privileges to {self.user2_name};"
        res = primary_sh.execut_db_sql(create_user,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, res)
        self.assertIn(self.constant.ALTER_ROLE_SUCCESS_MSG, res)
        alter_sql = f'alter procedure {self.proc_name}() owner to {self.user2_name};'
        res = primary_sh.execut_db_sql(alter_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertEqual(self.constant.ALTER_FUNCTION_SUCCESS_MSG, res)
        res = primary_sh.execut_db_sql(find_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.user2_name, res)

        text = '-----step6: 删除用户; expect: 合理报错-----'
        log.info(text)
        drop_user = f'drop user {self.user2_name};'
        res = primary_sh.execut_db_sql(drop_user,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(f'ERROR:  role "{self.user2_name}" cannot be dropped ' \
            r'because some objects depend on it', res)

    def tearDown(self):
        text = '-----step7: 清理环境; expect: 成功-----'
        log.info(text)
        drop_proc = f'''drop proc if exists {self.proc_name};'''
        res = primary_sh.execut_db_sql(drop_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_PROCEDURE_SUCCESS_MSG, res)
        drop_user = f'drop user if exists {self.user2_name};' \
            f'drop user if exists {self.user1_name};'
        res = primary_sh.execut_db_sql(drop_user, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, res)